#include <iostream>
#include <mysql/mysql.h>
#include <string>
#include <vector>
#include <memory>
#include <stdexcept>

/*
MySQL Installation and Basic Commands on Ubuntu:

1. Installation:
   sudo apt update
   sudo apt install mysql-server
   sudo mysql_secure_installation

2. Basic Commands:
   - Start/Stop MySQL service:
     sudo service mysql start/stop/restart
   - Login to MySQL:
     mysql -u root -p
   - Create new user:
     CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
   - Grant privileges:
     GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
   - Create database:
     CREATE DATABASE test_db;
   - Show databases:
     SHOW DATABASES;
   - Use database:
     USE database_name;
   - Show tables:
     SHOW TABLES;
*/

class MySQLConnection {
private:
    MYSQL* conn;
    std::string host;
    std::string user;
    std::string password;
    std::string database;
    unsigned int port;

public:
    MySQLConnection(const std::string& host = "localhost",
                   const std::string& user = "app_user",
                   const std::string& password = "Aa123456#",
                   const std::string& database = "test_db",
                   unsigned int port = 3306)
        : host(host), user(user), password(password), database(database), port(port) {
        conn = mysql_init(nullptr);
        if (!conn) {
            throw std::runtime_error("MySQL initialization failed");
        }
    }

    ~MySQLConnection() {
        if (conn) {
            mysql_close(conn);
        }
    }

    bool connect() {
        if (!mysql_real_connect(conn, host.c_str(), user.c_str(), password.c_str(),
                              database.c_str(), port, nullptr, CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS)) {
            std::cerr << "Connection failed: " << mysql_error(conn) << std::endl;
            return false;
        }
        return true;
    }

    bool executeQuery(const std::string& query) {
        if (mysql_query(conn, query.c_str()) != 0) {
            std::cerr << "Query failed: " << mysql_error(conn) << std::endl;
            return false;
        }

         // 处理所有结果集（关键）
        do {
            MYSQL_RES* result = mysql_store_result(conn);
            if (result) {
                mysql_free_result(result); // 释放结果内存
            }
        } while (mysql_next_result(conn) == 0);

    return true;
    
        return true;
    }

    MYSQL_RES* getResult() {
        return mysql_store_result(conn);
    }

    void freeResult(MYSQL_RES* result) {
        if (result) {
            mysql_free_result(result);
        }
    }
};

// Best Practices:
// 1. Use RAII for resource management
// 2. Use prepared statements to prevent SQL injection
// 3. Handle errors appropriately
// 4. Use connection pooling in production
// 5. Implement proper logging
// 6. Use transactions for multiple related operations
// 7. Implement proper connection retry logic
// 8. Use proper indexing for better performance
// 9. Implement proper error handling and recovery
// 10. Use proper data types and constraints

void testBasicOperations() {
    try {
        MySQLConnection conn;
        if (!conn.connect()) {
            return;
        }

        // Create table
        std::string createTable = R"(
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        )";
        conn.executeQuery(createTable);

        // Insert data
        std::string insertData = R"(
            INSERT INTO users (username) VALUES
            ('test_user1'),
            ('test_user2')
        )";
        conn.executeQuery(insertData);

        // Query data
        std::string selectData = "SELECT * FROM users";
        conn.executeQuery(selectData);
        MYSQL_RES* result = conn.getResult();
        
        if (result) {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(result))) {
                std::cout << "ID: " << row[0] 
                          << ", Username: " << row[1]
                          << ", Created: " << row[2] << std::endl;
            }
            conn.freeResult(result);
        }

        // Update data
        std::string updateData = "UPDATE users SET username = 'updated_user' WHERE id = 1";
        conn.executeQuery(updateData);

        // Delete data
        std::string deleteData = "DELETE FROM users WHERE id = 2";
        conn.executeQuery(deleteData);

        // Drop table (cleanup)
        std::string dropTable = "DROP TABLE IF EXISTS users";
        conn.executeQuery(dropTable);

    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

void testTransactions() {
    try {
        MySQLConnection conn;
        if (!conn.connect()) {
            return;
        }

        // Start transaction
        conn.executeQuery("START TRANSACTION");

        try {
            // Create accounts table
            std::string createTable = R"(
                CREATE TABLE IF NOT EXISTS accounts (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    balance DECIMAL(10,2) NOT NULL
                )
            )";
            conn.executeQuery(createTable);

            // Insert sample data
            std::string insertData = R"(
                INSERT INTO accounts (user_id, balance) VALUES
                (1, 1000.00),
                (2, 500.00)
            )";
            conn.executeQuery(insertData);

            // Transfer money (atomic operation)
            std::string transferMoney = R"(
                UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
                UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
            )";

            // std::string transferMoney = R"(
            //     UPDATE accounts SET balance = balance - 100 WHERE user_id = 1
            // )";
            conn.executeQuery(transferMoney);

            // Commit transaction
            conn.executeQuery("COMMIT");

        } catch (const std::exception& e) {
            // Rollback on error
            conn.executeQuery("ROLLBACK");
            throw;
        }

        // Cleanup
        // conn.executeQuery("DROP TABLE IF EXISTS accounts");

    } catch (const std::exception& e) {
        std::cerr << "Transaction error: " << e.what() << std::endl;
    }
}

int main() {
    // Initialize MySQL library
    if (mysql_library_init(0, nullptr, nullptr) != 0) {
        std::cerr << "MySQL library initialization failed" << std::endl;
        return 1;
    }

    // Run tests
    // std::cout << "Testing basic operations..." << std::endl;
    // testBasicOperations();

    std::cout << "\nTesting transactions..." << std::endl;
    testTransactions();

    // Cleanup MySQL library
    mysql_library_end();
    return 0;
}